package stock.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class GetSelect {
	private Statement st = null;
	private Connection con = null;
	private ResultSet rs = null;
	Coneccion coneccion = new Coneccion();

	public ResultSet getValues(String query) throws Exception {
		try {
			con = coneccion.getConnectionBase();
			st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = st.executeQuery(query);
		} catch (SQLException e) {
			throw new Exception("GetSelects.getAll()" + "<br>" + e + "<br>"
					+ query);
		}
		return rs;
	}

	public ResultSet getStockConsolidado(Connection conn) throws Exception {
		String query = "select total.id, total.descripcion, sum(total.cantidad) from (SELECT * FROM bird b union select * from jabbar j) as total group by id;";
		try {
			// con = base.getConnection();
			st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = st.executeQuery(query);
		} catch (SQLException e) {
			throw new Exception("GetSelects.getAll()" + "<br>" + e + "<br>"
					+ query);
		}
		return rs;
	}

	public ResultSet getMovimientosConsolidado(Connection conn)
			throws Exception {
		String query = "select id, descripcion, sum(cantidad) from (SELECT id, descripcion, sum(cantidad) cantidad FROM bird2 b group by id union select id, descripcion, sum(cantidad) cantidad from jabbar2 j group by id) as total group by id";
		try {
			// con = base.getConnection();
			st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = st.executeQuery(query);
		} catch (SQLException e) {
			throw new Exception("GetSelects.getAll()" + "<br>" + e + "<br>"
					+ query);
		}
		return rs;
	}

	public ResultSet getMovimientosAndStock(Connection conn) throws Exception {
		String query = "select total.id, total.descripcion, total.stock, sum(total.movimientos) movimientos from (SELECT s.id, s.descripcion, s.cantidad as stock, 0 as movimientos FROM stock.stock s union SELECT m.id, m.descripcion, 0 as stock, m.cantidad as movimientos FROM stock.movimientos m) as total group by total.id";
		try {
			// con = base.getConnection();
			st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = st.executeQuery(query);
		} catch (SQLException e) {
			throw new Exception("GetSelects.getAll()" + "<br>" + e + "<br>"
					+ query);
		}
		return rs;
	}

	public void cerrarCon() throws Exception {
		if (rs != null) {
			rs.close();
		}
		if (st != null) {
			st.close();
		}
		if (con != null) {
			con.close();
		}
	}
}
